package com.tools.hry.xls.analysis.util;


import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ReadBigExcelUtils {
    private List<Object> headers = new ArrayList<>();
    private List<List> datas = new ArrayList<>();
    private static StylesTable stylesTable;

    public void processOneSheet(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();
        stylesTable = r.getStylesTable();
        XMLReader parser = fetchSheetParser(sst);

        // To look up the Sheet Name / Sheet Order / rID,
        //  you need to process the core Workbook stream.
        // Normally it's of the form rId# or rSheet#
        InputStream sheet2 = r.getSheet("rId1");
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();
    }

//    public void processAllSheets(String filename) throws Exception {
//        OPCPackage pkg = OPCPackage.open(filename);
//        XSSFReader r = new XSSFReader(pkg);
//        SharedStringsTable sst = r.getSharedStringsTable();
//
//        XMLReader parser = fetchSheetParser(sst);
//
//        Iterator<InputStream> sheets = r.getSheetsData();
//        while (sheets.hasNext()) {
//            System.out.println("Processing new sheet:\n");
//            InputStream sheet = sheets.next();
//            InputSource sheetSource = new InputSource(sheet);
//            parser.parse(sheetSource);
//            sheet.close();
//            System.out.println("");
//        }
//    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
                XMLReaderFactory.createXMLReader(
                        "com.sun.org.apache.xerces.internal.parsers.SAXParser"
                );
        ContentHandler handler = new SheetHandler(sst, headers, datas);
        parser.setContentHandler(handler);
        return parser;
    }

    /**
     * See org.xml.sax.helpers.DefaultHandler javadocs
     */
    private static class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private List<Object> headers;
        private List<List> datas;//存放所有行数据
        private List data = new ArrayList();//存入一行数据
        private int currRowNum = 0;//当前行
        private String currXY;//当前位置
        private boolean isNewRow = false;//前一行（历史行）
//        private boolean dateFlag;//标记为日期类型
//        private boolean numberFlag;//标记为日期类型
        private int length = 0;
        private String lastContents;
        private boolean nextIsString;

        private CellDataType nextDataType = CellDataType.SSTINDEX;
        private final DataFormatter formatter = new DataFormatter();
        private short formatIndex;
        private String formatString;
        //用一个enum表示单元格可能的数据类型
        enum CellDataType{
            BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
        }

        private SheetHandler(SharedStringsTable sst, List<Object> headers,
                             List<List> datas) {
            this.sst = sst;
            this.headers = headers;
            this.datas = datas;
        }

        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {
            // c => cell
            if (name.equals("c")) {
                // Print the cell reference
                System.out.println(attributes.getValue("r") + " - ");
                currXY=attributes.getValue("r");
                // Figure out if the value is an index in the SST
                String cellType = attributes.getValue("t");
                if (cellType != null && cellType.equals("s")) {
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }
                this.setFlagData(attributes);
                int convertRowNum = convertRowNum(attributes.getValue("r"));
                if (convertRowNum != currRowNum) {//是否是换行了
                    isNewRow = true;
                    currRowNum = convertRowNum;
                    length = 0;
                } else {
                    isNewRow = false;
                }
                length++;
            } else if (name.equals("row")) {

            }
            // Clear contents cache
            lastContents = "";
        }

        private void setFlagData(Attributes attributes){
            // 日期格式
            nextDataType = CellDataType.NUMBER;
            formatIndex = -1;
            formatString = null;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");
            if ("b".equals(cellType)){
                nextDataType = CellDataType.BOOL;
            }else if ("e".equals(cellType)){
                nextDataType = CellDataType.ERROR;
            }else if ("inlineStr".equals(cellType)){
                nextDataType = CellDataType.INLINESTR;
            }else if ("s".equals(cellType)){
                nextDataType = CellDataType.SSTINDEX;
            }else if ("str".equals(cellType)){
                nextDataType = CellDataType.FORMULA;
            }
            if (cellStyleStr != null){
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                formatIndex = style.getDataFormat();
                formatString = style.getDataFormatString();
                if ("m/d/yy" == formatString){
                    nextDataType = CellDataType.DATE;
                    //full format is "yyyy-MM-dd hh:mm:ss.SSS";
                    formatString = "yyyy-MM-dd";
                }
                if (formatString == null){
                    nextDataType = CellDataType.NULL;
                    formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                }
            }



        }

        private int convertRowNum(String cellIndex) {
            String regEx = "[^0-9]";
            Pattern p = Pattern.compile(regEx);
            Matcher m = p.matcher(cellIndex);
//            System.out.println(m.replaceAll("").trim());
            return Integer.parseInt(m.replaceAll("").trim());
        }

        public void endElement(String uri, String localName, String name)
                throws SAXException {
            // Process the last contents as required.
            // Do now, as characters() may be called more than once
//            System.out.println(currRowNum + ":" + isNewRow);
            if (nextIsString) {
                int idx = Integer.parseInt(lastContents);
               // System.out.print("["+lastContents+"]");
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                nextIsString = false;
            }

            if(currXY!=null&&currXY!=""&&currXY.length()>0){
                String value = lastContents.trim();
                value = value.equals("") ? " " : value;
                // v => contents of a cell
                // Output after we've seen the string contents
//                if (name.equals("v")) {
                    //System.out.print("["+lastContents+"]");
//                }
                try {
                    value= this.getDataValue(value,"");
//                    // 日期格式处理
//                    if (dateFlag) {
//                        Date date = HSSFDateUtil.getJavaDate(Double.valueOf(value));
//                        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
//                        value = dateFormat.format(date);
//                        dateFlag=false;
//                    }
//                    // 数字类型处理
//                    if (numberFlag) {
//                        BigDecimal bd = new BigDecimal(value);
//                        value = bd.setScale(3, BigDecimal.ROUND_UP).toString();
//                        numberFlag=false;
//                    }
                } catch (Exception e) {
                    // 转换失败仍用读出来的值
                }
                if (currRowNum == 1) {//header
                    headers.add(value);
//                    System.out.println("===============");
                } else {
                    if (isNewRow && data.size()>0) {
//                        System.out.println("===============");
                        //如果是换行了将data添加到datas集合中，并清空data
                        datas.add(data);
                        data=new ArrayList();
                    }
                    if (length > 0) {
                        data.add(value);
                    }
                }
                currXY="";
            }

        }


        /**
         * 根据数据类型获取数据
         * @param value
         * @param thisStr
         * @return
         */
        public String getDataValue(String value, String thisStr)

        {
            switch (nextDataType)
            {
                //这几个的顺序不能随便交换，交换了很可能会导致数据错误
                case BOOL:
                    char first = value.charAt(0);
                    thisStr = first == '0' ? "FALSE" : "TRUE";
                    break;
                case ERROR:
                    thisStr = "\"ERROR:" + value.toString() + '"';
                    break;
                case FORMULA:
                    thisStr = '"' + value.toString() + '"';
                    break;
                case INLINESTR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                    thisStr = rtsi.toString();
                    rtsi = null;
                    break;
                case SSTINDEX:
                    String sstIndex = value.toString();
                    thisStr = value.toString();
                    break;
                case NUMBER:
//                    if("IF(ISNUMBER(G1),G1,1)"){
//
//                    }
                    if (formatString != null){
                        thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                    }else{
                        thisStr = value;
                    }
                    thisStr = thisStr.replace("_", "").trim();
                    break;
                case DATE:
                    try{
                        thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                    }catch(NumberFormatException ex){
                        thisStr = value.toString();
                    }
                    thisStr = thisStr.replace(" ", "");
                    break;
                default:
                    thisStr = "";
                    break;
            }
            return thisStr;
        }


        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
    }

    public static void main(String[] args) throws Exception {
        ReadBigExcelUtils example = new ReadBigExcelUtils();
        System.out.println("11");
        long one=System.currentTimeMillis();
        example.processOneSheet("e://非卡单1-3月 (2).xlsx");
        long two=System.currentTimeMillis();
        // example.processAllSheets(args[0]);
        System.out.println("================");
        System.out.println((two-one));
    }
}
